﻿Imports System.Data
Imports System.Data.SqlClient
'Hacemos referencia al proyecto BusinessEntities
Imports BE = BusinessEntities

Public Class ServicioDAO
    Dim conexion As SqlConnection
    Dim comando As SqlDataAdapter
    Public Shared errores As String
    Dim com As SqlCommand

    'Instanciamos la clase Conexion
    Dim cn As New Conexion


    Public Function ListarServicios() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_LISTARSERVICIOS", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Dim ds As New DataSet
            comando.Fill(ds, "LosServicios")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function BuscarServicio(ByVal objeto As BE.ServicioBE) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_BUSCARUNSERVICIO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el paràmetro del stored procedure
            comando.SelectCommand.Parameters.Add _
            (New SqlParameter("@SERVICIO", SqlDbType.Char, 6))

            comando.SelectCommand.Parameters("@SERVICIO").Direction = ParameterDirection.Input
            comando.SelectCommand.Parameters("@SERVICIO").Value = objeto.codigo


            'Dim ds As New DataSet
            comando.Fill(ds, "Servicio")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function DetalleServicio(ByVal objeto As BE.ServicioBE) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_BUSCARUNSERVICIO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el paràmetro del stored procedure
            comando.SelectCommand.Parameters.Add _
            (New SqlParameter("@SERVICIO", SqlDbType.Char, 6))

            comando.SelectCommand.Parameters("@SERVICIO").Direction = ParameterDirection.Input
            comando.SelectCommand.Parameters("@SERVICIO").Value = objeto.codigo


            'Dim ds As New DataSet
            comando.Fill(ds, "ElServicio")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function BuscarServiciosxTipo(ByVal objeto As BE.ServicioBE) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_BUSCARSERVICIOSXTIPO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el paràmetro del stored procedure
            comando.SelectCommand.Parameters.Add _
            (New SqlParameter("@TIPO", SqlDbType.Char, 3))

            comando.SelectCommand.Parameters("@TIPO").Direction = ParameterDirection.Input
            comando.SelectCommand.Parameters("@TIPO").Value = objeto.tiposervicio


            'Dim ds As New DataSet
            comando.Fill(ds, "ServiciosxTipo")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function BuscarServiciosxCriterio(ByVal objeto As BE.ServicioBE) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_BUSCARSERVICIOSXCRITERIO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el paràmetro del stored procedure
            comando.SelectCommand.Parameters.Add _
            (New SqlParameter("@CRITERIO", SqlDbType.VarChar, 30))

            comando.SelectCommand.Parameters("@CRITERIO").Direction = ParameterDirection.Input
            comando.SelectCommand.Parameters("@CRITERIO").Value = objeto.nombreservicio



            'Dim ds As New DataSet
            comando.Fill(ds, "ServiciosxCriterio")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function InsertarServicio(ByVal objeto As BE.ServicioBE) As Boolean

        Try
            conexion = cn.Conectar
            com = New SqlCommand("SP_INSERTARSERVICIO", conexion)
            com.CommandType = CommandType.StoredProcedure

            'Definimos los parámetros de entrada
            com.Parameters.Add(New SqlParameter("@TIPO", SqlDbType.Char, 3))
            com.Parameters("@TIPO").Direction = ParameterDirection.Input
            com.Parameters("@TIPO").Value = objeto.tiposervicio

            com.Parameters.Add(New SqlParameter("@PROVEEDOR", SqlDbType.Char, 3))
            com.Parameters("@PROVEEDOR").Direction = ParameterDirection.Input
            com.Parameters("@PROVEEDOR").Value = objeto.proveedor

            com.Parameters.Add(New SqlParameter("@NOMBRE", SqlDbType.VarChar, 30))
            com.Parameters("@NOMBRE").Direction = ParameterDirection.Input
            com.Parameters("@NOMBRE").Value = objeto.nombreservicio

            com.Parameters.Add(New SqlParameter("@CARACTERISTICAS", SqlDbType.VarChar, 250))
            com.Parameters("@CARACTERISTICAS").Direction = ParameterDirection.Input
            com.Parameters("@CARACTERISTICAS").Value = objeto.caracteristicas

            com.Parameters.Add(New SqlParameter("@PRECIO", SqlDbType.Decimal))
            com.Parameters("@PRECIO").Direction = ParameterDirection.Input
            com.Parameters("@PRECIO").Value = objeto.precio

            com.Parameters.Add(New SqlParameter("@STOCK", SqlDbType.Int))
            com.Parameters("@STOCK").Direction = ParameterDirection.Input
            com.Parameters("@STOCK").Value = objeto.stock

            com.Parameters.Add(New SqlParameter("@FOTO", SqlDbType.VarChar, 100))
            com.Parameters("@FOTO").Direction = ParameterDirection.Input
            com.Parameters("@FOTO").Value = objeto.foto


            conexion.Open()
            com.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            errores = ex.Message
            Return False
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            'Liberamos recursos
            conexion.Dispose()
            com.Dispose()
        End Try

    End Function


    Public Function ActualizarServicio(ByVal objeto As BE.ServicioBE) As Boolean

        Try
            conexion = cn.Conectar
            com = New SqlCommand("SP_ACTUALIZARSERVICIO", conexion)
            com.CommandType = CommandType.StoredProcedure

            'Definimos los parámetros de entrada

            com.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Char, 6))
            com.Parameters("@CODIGO").Direction = ParameterDirection.Input
            com.Parameters("@CODIGO").Value = objeto.codigo


            com.Parameters.Add(New SqlParameter("@TIPO", SqlDbType.Char, 3))
            com.Parameters("@TIPO").Direction = ParameterDirection.Input
            com.Parameters("@TIPO").Value = objeto.tiposervicio

            com.Parameters.Add(New SqlParameter("@PROVEEDOR", SqlDbType.Char, 3))
            com.Parameters("@PROVEEDOR").Direction = ParameterDirection.Input
            com.Parameters("@PROVEEDOR").Value = objeto.proveedor

            com.Parameters.Add(New SqlParameter("@NOMBRE", SqlDbType.VarChar, 30))
            com.Parameters("@NOMBRE").Direction = ParameterDirection.Input
            com.Parameters("@NOMBRE").Value = objeto.nombreservicio

            com.Parameters.Add(New SqlParameter("@CARACTERISTICAS", SqlDbType.VarChar, 250))
            com.Parameters("@CARACTERISTICAS").Direction = ParameterDirection.Input
            com.Parameters("@CARACTERISTICAS").Value = objeto.caracteristicas

            com.Parameters.Add(New SqlParameter("@PRECIO", SqlDbType.Decimal))
            com.Parameters("@PRECIO").Direction = ParameterDirection.Input
            com.Parameters("@PRECIO").Value = objeto.precio

            com.Parameters.Add(New SqlParameter("@STOCK", SqlDbType.Int))
            com.Parameters("@STOCK").Direction = ParameterDirection.Input
            com.Parameters("@STOCK").Value = objeto.stock

            com.Parameters.Add(New SqlParameter("@FOTO", SqlDbType.VarChar, 100))
            com.Parameters("@FOTO").Direction = ParameterDirection.Input
            com.Parameters("@FOTO").Value = objeto.foto

            conexion.Open()
            com.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            errores = ex.Message
            Return False
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            'Liberamos recursos
            conexion.Dispose()
            com.Dispose()
        End Try

    End Function

    Public Function EliminarServicio(ByVal objeto As BE.ServicioBE) As Boolean

        Try
            conexion = cn.Conectar
            com = New SqlCommand("SP_ELIMINARSERVICIO", conexion)

            com.CommandType = CommandType.StoredProcedure

            'Definimos los parámetros de entrada

            com.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Char, 6))
            com.Parameters("@CODIGO").Direction = ParameterDirection.Input
            com.Parameters("@CODIGO").Value = objeto.codigo

            conexion.Open()
            com.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            errores = ex.Message
            Return False
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            'Liberamos recursos
            conexion.Dispose()
            com.Dispose()
        End Try

    End Function

    Public Function ListarTipoServicios() As DataSet  'categorias
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_LISTARTIPO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Dim ds As New DataSet
            comando.Fill(ds, "LosTipoServicios") 'categorias

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function BuscarDescripcionTipoServicios(ByVal objeto As BE.ServicioBE) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            comando = New SqlDataAdapter("SP_OBTENERDESTIPO", conexion)
            comando.SelectCommand.CommandType = CommandType.StoredProcedure

            'Definimos el paràmetro del stored procedure
            comando.SelectCommand.Parameters.Add _
            (New SqlParameter("@TIPO", SqlDbType.Char, 3))

            comando.SelectCommand.Parameters("@TIPO").Direction = ParameterDirection.Input
            comando.SelectCommand.Parameters("@TIPO").Value = objeto.tiposervicio


            'Dim ds As New DataSet
            comando.Fill(ds, "descripcionTipoServicios")

        Catch ex As Exception
            errores = ex.Message
        Finally
            conexion.Dispose()
            comando.Dispose()
            ds.Dispose()
        End Try

        Return ds

    End Function

    Public Function ChapaError() As String
        Return errores
    End Function
End Class
